#include <math.h>
#include "dbpage.h"
#include <iostream>
#include <map>
#include <stdlib.h>

using namespace std;

DbPage::DbPage()
{
    records = 50;
    first = 1;
    previous = 0;
    last = 0;
    total = 0;
    table = "tickets";
    columns = "number,date,customer,phone,ip,status,active";
    orderby = "number asc";
    columnList.push_back("number");
    columnList.push_back("date");
    columnList.push_back("customer");
    columnList.push_back("phone");
    columnList.push_back("ip");
    columnList.push_back("status");
    columnList.push_back("active");
    model = new vector< vector<string> >;
}
bool DbPage::isValid()
{
    return (total > 0 && total >= previous && (previous % records == 0));
}
bool DbPage::firstOk()
{
    return (previous >= records) ? true : false;
}
bool DbPage::previousOk()
{
    return (previous >= 2 * records) ? true : false;
}
bool DbPage::nextOk()
{
    return (previous < total - 2 * records) ? true : false;
}
bool DbPage::lastOk()
{
    return (previous < total - records) ? true : false;
}
int DbPage::previousPage()
{
    return previousOk() ? page - 1 : page;
}
int DbPage::nextPage()
{
    return nextOk() ? page + 1 : page;
}
int DbPage::lastPage()
{
    return (int)ceil(total / (records * 1.0f));
}
void DbPage::clean()
{
    mysql_free_result(res);
    mysql_close(conn);
}

vector< vector<string> >* DbPage::search(string str, int pag, string orderBy)
{
    searchText = str;
    if (orderBy!="")
        orderby = orderBy;

    total = searchCount();

    page = lastPage();
    if (pag != 0 && pag < page)
        page = pag;

    previous = (page - 1) * records;
    first = previous + 1;
    last = previous + records;
    if (last > total)
        last = total;

    if (isValid())
        model = search();
    //cout <<"good search:"  <<  endl;
    return model;
}

vector< vector<string> >* DbPage::search()
{
    //cout <<"inside search:"  <<  endl;
    sql = createSql();
    //cout <<"new sql:"  << sql << endl;
    model = executeSql(sql);
    return model;
}

int DbPage::searchCount()
{
    sql = "select count(*) as total from " + table;
    whereSql = "";
    if (searchText != "")
    {
        whereSql = " where "+columnList[0]+" like '%"+ searchText +"%'";
        for (int i=1;i<columnList.size()-1; i++)
            whereSql += " or "+ columnList[i] +" like '%"+ searchText +"%'";
        sql += whereSql;
    }

    model = executeSql(sql);

    return atoi(model->at(0).at(0).c_str());
}
vector< vector<string> >* DbPage::executeSql(string sql)
{
    cout << sql << endl;

    model = new vector< vector<string> >;
    if(connect())
    {
        if (mysql_query(conn, sql.c_str()))
        {
            cout <<"error executing query: "<< mysql_error(conn);
            exit(1);
        }
        //cout <<"before result "  << endl;
        res = mysql_use_result(conn);
        //result = mysql_store_result(&mysql); ???
        //cout <<"after result "  << endl;
        unsigned int num_fields = mysql_num_fields(res);
        cout <<"number of fields:"  << num_fields<< endl;

        int record = 0;
        while ((row = mysql_fetch_row(res)) != NULL)
        {
            cout <<"rec: " << record;
            vector<string> v;
            for(int i = 0; i < num_fields; i++)
            {
                cout << row[i] <<"\t";
                string s = row[i];
                v.push_back(s);
            }
            model->push_back(v);
            record++;
        }
        cout <<"ok: "  << endl;
    }
    cout <<"salida"  << endl;
    clean();
    return model;
}
string DbPage::createSql()
{
    sql = "select ";
    sql.append(columns).append(" from ").append(table);
    cout <<"sql:"  << sql<< endl;
    if (searchText != "")
        sql.append(whereSql);
    sql.append(" order by ").append(orderby).append(" limit ");
    cout <<"sql:"  << sql<< endl;

    sql.append(numberToString(previous));
    cout <<"sql:"  << sql<< endl;
    sql.append(",").append(numberToString(records));
    return sql;
}


bool DbPage::connect()
{
    char *server = "localhost";
    char *user = "root";
    char *password = "";
    char *database = "tickets";

    conn = mysql_init(NULL);

    if (!mysql_real_connect(conn, server, user, password, database, 0, NULL, 0))
    {
        cout << mysql_error(conn);
        return false;
    }
    return true;
}

string DbPage::numberToString(int number)
{
    stringstream out;
    out << number;
    return  out.str();

}
